﻿Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlServerCe
Imports System.Text
Imports System.Collections.Generic
Imports System.IO.Path
Imports System.Reflection.Assembly

Namespace Conexoes

#Region "SQL Compact Edition"
    Public Class conexaoSQLServerCE

        Sub New() 'METODO CONSTRUTOR

        End Sub

        'objeto para conexao


        Private conexao As New SqlCeConnection()

        Dim fileBD As String = "data source = " & GetDirectoryName(GetExecutingAssembly().GetName().CodeBase) & "\\Leituras.sdf"
        'Dim transacao As SqlCeTransaction = conexao.BeginTransaction

        Public Sub AbreConexao()

            Try

                If (conexao.State = ConnectionState.Closed) Then

                    Dim strConexao As String = fileBD

                    conexao = New SqlCeConnection(strConexao)

                    conexao.Open()

                End If

            Catch ex As Exception

                Throw New Exception("Ocorreu um problema na conexão com o base de dados, devido ao seguinte erro: " + ex.Message)

            End Try

        End Sub

        Public Sub FechaConexao()
            Try

                If (conexao.State = ConnectionState.Open) Then

                    conexao.Close()

                End If

            Catch ex As Exception

                Throw New Exception("Ocorreu um problema na conexão com o base de dados, devido ao seguinte erro: " + ex.Message)

            End Try

        End Sub

        Private Sub CriaBD(ByVal nomeBD As String)
            Dim sqlengine As New SqlCeEngine(fileBD)

            sqlengine.CreateDatabase()

        End Sub

        Public Function PreencheDataSet(ByVal dts As DataSet, ByVal sql As String) As SqlCeDataAdapter

            Try

                Dim da As New SqlCeDataAdapter(sql, conexao)

                da.Fill(dts)

                Return da

            Catch ex As Exception

                Throw New Exception("Ocorreu um problema na conexão com o base de dados, devido ao seguinte erro: " + ex.Message)

            End Try

        End Function

        Public Function PreencheDataTable(ByVal sql As String) As DataTable

            Dim dt As New DataTable

            Try

                Dim da As New SqlCeDataAdapter(sql, conexao)

                da.Fill(dt)

                Return dt

            Catch ex As Exception

                Throw New Exception("Ocorreu um problema na conexão com o base de dados, devido ao seguinte erro: " + ex.Message)

            End Try

        End Function

        Public Function PreencheCeDataTable(ByVal sql As String, ByVal prm As Hashtable)

            Dim dt As New DataTable

            Try

                Dim cmd As New SqlCeCommand(sql, conexao)

                If prm.Count > 0 Then
                    For Each chave As Object In prm.Keys
                        cmd.Parameters.AddWithValue(chave.ToString(), prm(chave).GetType())
                        cmd.Parameters(chave.ToString()).Value = prm(chave)
                    Next
                End If

                Dim da As New SqlCeDataAdapter(cmd)
                da.Fill(dt)

            Catch ex As Exception

                Throw New Exception("Ocorreu um problema na conexão com o base de dados, devido ao seguinte erro: " + ex.Message)

            End Try

            Return dt

        End Function

        Public Function PreencheDataReader(ByVal sql As String) As SqlCeDataReader

            Dim cmd As New SqlCeCommand(sql, conexao)
            Dim dr As SqlCeDataReader = cmd.ExecuteReader()

            Return dr

        End Function

        Public Function PreencheDataReader(ByVal sql As String, ByVal prm As Hashtable) As SqlCeDataReader

            Dim cmd As New SqlCeCommand(sql, conexao)

            If prm.Count > 0 Then
                For Each chave As Object In prm.Keys
                    cmd.Parameters.AddWithValue(chave.ToString(), prm(chave).GetType())
                    cmd.Parameters(chave.ToString()).Value = prm(chave)
                Next
            End If

            Dim dr As SqlCeDataReader = cmd.ExecuteReader()

            Return dr

        End Function

        Public Sub ExecutaComandoSQL(ByVal sql As String)

            Try

                Dim cmd As New SqlCeCommand(sql, conexao)

                cmd.ExecuteNonQuery()

            Catch ex As Exception

                Throw New Exception("Ocorreu um problema na conexão com o base de dados, devido ao seguinte erro: " + ex.Message)

            End Try

        End Sub

        Public Sub ExecutaComandoSQL(ByVal sql As String, ByVal prm As Hashtable)

            Try

                Dim cmd As New SqlCeCommand(sql, conexao)

                If prm.Count > 0 Then
                    For Each chave As Object In prm.Keys
                        cmd.Parameters.AddWithValue(chave.ToString(), prm(chave).GetType())
                        cmd.Parameters(chave.ToString()).Value = prm(chave)
                    Next
                End If

                cmd.ExecuteNonQuery()

            Catch ex As Exception

                Throw New Exception("Ocorreu um problema na conexão com o base de dados, devido ao seguinte erro: " + ex.Message)

            End Try

        End Sub

        Public Sub IniciaTransacao()

            Try

                If (conexao.State = ConnectionState.Open) Then

                    'transacao = conexao.BeginTransaction

                End If

            Catch ex As Exception

                Throw New Exception("Ocorreu um problema na conexão com a base da dados, devido ao seguinte erro: " + ex.Message)

            End Try

        End Sub

        Public Sub FinalizaTransacao(ByRef flag As Boolean)


            If flag = False Then

                'transacao.Commit()

            Else

                'transacao.Rollback()

            End If

        End Sub

    End Class
#End Region

#Region "SQL Server"
    Public Class conexaoSQLServer

        Sub New() 'METODO CONSTRUTOR

        End Sub

        'objeto para conexao

        Private conexaoSQL As New SqlConnection()

        Dim strBD As String = "Data Source=SFSGP01;Initial Catalog=Leituras;Persist Security Info=True;User ID='coleta';Password='coleta'"
        'Dim transacao As SqlCeTransaction = conexao.BeginTransaction

        Public Sub AbreConexao()

            Try

                If (conexaoSQL.State = ConnectionState.Closed) Then

                    Dim strConexao As String = strBD

                    conexaoSQL = New SqlConnection(strConexao)

                    conexaoSQL.Open()

                End If

            Catch ex As Exception

                Throw New Exception("Ocorreu um problema na conexão com o base de dados, devido ao seguinte erro: " + ex.Message)

            End Try

        End Sub

        Public Sub FechaConexao()
            Try

                If (conexaoSQL.State = ConnectionState.Open) Then

                    conexaoSQL.Close()

                End If

            Catch ex As Exception

                Throw New Exception("Ocorreu um problema na conexão com o base de dados, devido ao seguinte erro: " + ex.Message)

            End Try

        End Sub

        Public Function PreencheDataSet(ByVal dts As DataSet, ByVal sql As String) As SqlDataAdapter

            Try

                Dim da As New SqlDataAdapter(sql, conexaoSQL)

                da.Fill(dts)

                Return da

            Catch ex As Exception

                Throw New Exception("Ocorreu um problema na conexão com o base de dados, devido ao seguinte erro: " + ex.Message)

            End Try

        End Function

        Public Sub PreencheDataTable(ByVal dt As DataTable, ByVal sql As String)

            Try

                Dim da As New SqlDataAdapter(sql, conexaoSQL)

                da.Fill(dt)

            Catch ex As Exception

                Throw New Exception("Ocorreu um problema na conexão com o base de dados, devido ao seguinte erro: " + ex.Message)

            End Try

        End Sub

        Public Function PreencheDataReader(ByVal sql As String) As SqlDataReader

            Dim cmd As New SqlCommand(sql, conexaoSQL)
            Dim dr As SqlDataReader = cmd.ExecuteReader()

            Return dr

        End Function

        Public Sub ExecutaComandoSQL(ByVal sql As String)

            Try

                Dim cmd As New SqlCommand(sql, conexaoSQL)

                cmd.ExecuteNonQuery()

            Catch ex As Exception

                Throw New Exception("Ocorreu um problema na conexão com o base de dados, devido ao seguinte erro: " + ex.Message)

            End Try

        End Sub

        Public Sub ExecutaComandoSQL(ByVal sql As String, ByVal prm As String, ByVal img As Object)

            Try

                Dim cmd As New SqlCommand(sql, conexaoSQL)

                cmd.Parameters.Add(prm, SqlDbType.Image)
                cmd.Parameters(prm).Value = img

                cmd.ExecuteNonQuery()

            Catch ex As Exception

                Throw New Exception("Ocorreu um problema na conexão com o base de dados, devido ao seguinte erro: " + ex.Message)

            End Try

        End Sub

        Public Sub IniciaTransacao()

            Try

                If (conexaoSQL.State = ConnectionState.Open) Then

                    'transacao = conexao.BeginTransaction

                End If

            Catch ex As Exception

                Throw New Exception("Ocorreu um problema na conexão com a base da dados, devido ao seguinte erro: " + ex.Message)

            End Try

        End Sub

        Public Sub FinalizaTransacao(ByRef flag As Boolean)


            If flag = False Then

                'transacao.Commit()

            Else

                'transacao.Rollback()

            End If

        End Sub

    End Class
#End Region

End Namespace
